package dao.impl;

import dao.ICountryDao;
import dao.IProvinceDao;
import jdbc_util.FirstApp;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import pojo.Country;
import pojo.Product;
import pojo.Province;

import java.sql.SQLException;
import java.util.List;

public class ProvinceDaoImpl implements IProvinceDao {
    @Override
    public void addProvince(Province province) {
        QueryRunner queryRunner = new QueryRunner();
        String sql = "insert into province (provincename,countryid) values (?,?)";
        try {
            queryRunner.update(FirstApp.getConn(), sql,
                    province.getProvinceName(), province.getCountry().getCountryid());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void updateProvince(Province province) {
        QueryRunner queryRunner = new QueryRunner();
        String sql = "update province set provincename=?,countryid=?";
        try {
            queryRunner.update(FirstApp.getConn(), sql,
                    province.getProvinceName(), province.getCountry().getCountryid());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteProvince(int id) {
        QueryRunner queryRunner = new QueryRunner();
        String sql = "delete from province where provinceid=?";
        try {
            queryRunner.update(FirstApp.getConn(), sql, id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public List<Province> findAll() {
        List<Province> provinces = null;
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select * from province";
        try {
            provinces = queryRunner.query(FirstApp.getConn(), sql, new BeanListHandler<>(Province.class));
            return provinces;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public Province findById(int id) {
        Province province = null;
        ICountryDao countryDao = new CountryDaoImpl();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select * from province where provinceid=?";
        try {
            Object[] query = queryRunner.query(FirstApp.getConn(), sql, new ArrayHandler(), id);
            if (query.length > 0) {
                province = new Province();
                province.setProvinceid((Integer) query[0]);
                province.setProvinceName((String) query[1]);
                Country country = countryDao.findById((Integer) query[2]);
                province.setCountry(country);
                return province;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public Province findByProvinceName(String name) {
        Province province = null;
        ICountryDao countryDao = new CountryDaoImpl();
        QueryRunner queryRunner = new QueryRunner();
        String sql = "select * from province where provincename=?";
        try {
            Object[] query = queryRunner.query(FirstApp.getConn(), sql, new ArrayHandler(), name);
            if (query.length > 0) {
                province = new Province();
                province.setProvinceid((Integer) query[0]);
                province.setProvinceName((String) query[1]);
                Country country = countryDao.findById((Integer) query[2]);
                province.setCountry(country);
                return province;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}
